import 'package:flutter/material.dart';
import 'package:fluttertoast/fluttertoast.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

import 'info_button.dart';

class AttachTest extends StatefulWidget {
  const AttachTest({super.key, required this.title});

  final String title;

  @override
  State<AttachTest> createState() => _AttachTestState();
}

class _AttachTestState extends State<AttachTest> {
  String? _attachQueryResult;
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: ListView(children: <Widget>[_attachWidget()]),
    );
  }

  Widget _attachWidget() {
    return InfoButton(
        title: "attach测试",
        info: _attachQueryResult,
        onTap: () async {
          final maps = await attachTest();
          setState(() {
            _attachQueryResult = maps.map((map) => map.toString()).join("\n");
          });
        });
  }

  Future<List<Map<String, Object?>>> attachTest() async {
    // 初始化两个数据库
    final db1 = await initDatabase('main.db');
    final db2 = await initDatabase('secondary.db');

    // 插入示例数据
    await db1.insert('users', {'name': 'Alice'});
    await db2.insert('orders', {'product': 'Laptop', 'user_id': 1});

    // 附加第二个数据库
    await attachDatabase(db1, db2, 'secondary');

    // 执行跨数据库查询
    final results = await db1.rawQuery('''
    SELECT users.name, orders.product 
    FROM users
    INNER JOIN secondary.orders ON users.id = orders.user_id
  ''');

    print('跨数据库查询结果:');
    for (final row in results) {
      print('${row['name']} 订购了 ${row['product']}');
    }

    // 分离数据库（可选）
    await db1.execute('DETACH DATABASE secondary');

    // 关闭数据库
    await db1.close();
    await db2.close();

    return results;
  }

// 初始化数据库并创建表
  Future<Database> initDatabase(String name) async {
    final path = join(await getDatabasesPath(), name);
    return openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        if (name == 'main.db') {
          await db.execute('''
          CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT
          )
        ''');
        } else {
          await db.execute('''
          CREATE TABLE orders (
            id INTEGER PRIMARY KEY,
            product TEXT,
            user_id INTEGER
          )
        ''');
        }
      },
    );
  }

  // 附加数据库的核心方法
  Future<void> attachDatabase(Database mainDb, Database secondaryDb, String alias) async {
    // 关闭目标数据库以释放文件锁
    await secondaryDb.close();
    // 获取附加数据库的绝对路径
    final path = join(await getDatabasesPath(), 'secondary.db');

    try {
      // 执行 ATTACH 命令
      await mainDb.execute('ATTACH DATABASE ? AS $alias', [path]);
      Fluttertoast.showToast(msg: '执行 ATTACH 命令成功');
    } catch (error) {
      Fluttertoast.showToast(msg: '执行 ATTACH 命令失败：${error.toString()}');
    }
  }
}
